package com.example.myapplication

import android.annotation.SuppressLint
import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
import android.util.Log
import android.widget.Button
import android.widget.EditText
import android.widget.TextView
import com.example.total.R

class DatabasePageActivity : AppCompatActivity() {

    public lateinit var dbHelper: MyDbHelper
    public lateinit var adapter: MyDbAdapter
    lateinit var queryS : String
    @SuppressLint("MissingInflatedId")
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_database_page)
        dbHelper = MyDbHelper(this)//表格搭建
        adapter = MyDbAdapter(this)//数据库页面所需适配器

        val btnCreateTable = findViewById<Button>(R.id.btn_create_table)
        btnCreateTable.setOnClickListener {
            createTable()
        }

        val btnAdd = findViewById<Button>(R.id.btn_add)
        btnAdd.setOnClickListener {
            var idNum=findViewById<EditText>(R.id.et_idNum).text.toString()
            val name=findViewById<EditText>(R.id.et_name).text.toString()
            val graph1=findViewById<EditText>(R.id.et_graph1).text.toString()
            val graph2=findViewById<EditText>(R.id.et_graph2).text.toString()
            val graph3=findViewById<EditText>(R.id.et_graph3).text.toString()
            val graph4=findViewById<EditText>(R.id.et_graph4).text.toString()
            val model1=findViewById<EditText>(R.id.et_model1).text.toString()
            val model2=findViewById<EditText>(R.id.et_model2).text.toString()
            val model3=findViewById<EditText>(R.id.et_model3).text.toString()
            val text1=findViewById<EditText>(R.id.et_text1).text.toString()
            val text2=findViewById<EditText>(R.id.et_text2).text.toString()
            val text3=findViewById<EditText>(R.id.et_text3).text.toString()
            val text4=findViewById<EditText>(R.id.et_text4).text.toString()
            addRecord(idNum,name, graph1, graph2, graph3, graph4, model1, model2, model3,text1, text2, text3, text4)
        }
        val btnUpdate = findViewById<Button>(R.id.btn_update)
        btnUpdate.setOnClickListener {
            val idNum=findViewById<EditText>(R.id.et_idNum).text.toString()
            val name=findViewById<EditText>(R.id.et_name).text.toString()
            val graph1=findViewById<EditText>(R.id.et_graph1).text.toString()
            val graph2=findViewById<EditText>(R.id.et_graph2).text.toString()
            val graph3=findViewById<EditText>(R.id.et_graph3).text.toString()
            val graph4=findViewById<EditText>(R.id.et_graph4).text.toString()
            val model1=findViewById<EditText>(R.id.et_model1).text.toString()
            val model2=findViewById<EditText>(R.id.et_model2).text.toString()
            val model3=findViewById<EditText>(R.id.et_model3).text.toString()
            val text1=findViewById<EditText>(R.id.et_text1).text.toString()
            val text2=findViewById<EditText>(R.id.et_text2).text.toString()
            val text3=findViewById<EditText>(R.id.et_text3).text.toString()
            val text4=findViewById<EditText>(R.id.et_text4).text.toString()
            addRecord(idNum,name, graph1, graph2, graph3, graph4, model1, model2, model3,text1, text2, text3, text4)
        }

        val btnDelete = findViewById<Button>(R.id.btn_delete)
        btnDelete.setOnClickListener {
            val idNum = findViewById<EditText>(R.id.et_idNum).text.toString()
            deleteRecord(idNum)
        }

        val btnQuery = findViewById<Button>(R.id.btn_query)
        btnQuery.setOnClickListener {
            val idNum = findViewById<EditText>(R.id.et_idNum).text.toString()
            queryRecord(idNum)
        }
    }

    private fun createTable() {
        dbHelper.writableDatabase
        // 这里不需要做任何事情，只要调用writableDatabase就会触发onCreate函数创建数据库和表格。
    }

    private fun addRecord(idNum: String?, name: String?,graph1: String?,graph2: String?,graph3: String?,graph4: String?,model1: String?,model2: String?,model3: String?,text1:String?,text2:String?,text3:String?,text4:String?) {
        val id = adapter.insert(idNum, name,graph1,graph2,graph3,graph4,model1,model2,model3,text1,text2,text3,text4)
        findViewById<TextView>(R.id.tv_result).text = "添加成功，id为$id"
    }

    private fun updateRecord(idNum: String?, name: String?,graph1: String?,graph2: String?,graph3: String?,graph4: String?,model1: String?,model2: String?,model3: String?,text1:String?,text2:String?,text3:String?,text4:String?) {
        val count = adapter.update(idNum, name,graph1,graph2,graph3,graph4,model1,model2,model3,text1,text2,text3,text4)
        findViewById<TextView>(R.id.tv_result).text = "更新了"+count+"条记录"
    }

    private fun deleteRecord(idNum: String?) {
        val count = adapter.delete(idNum)
        findViewById<TextView>(R.id.tv_result).text = "删除了"+count+"条记录"
    }

    fun queryRecord(id: String) {
        val projection = arrayOf(MyDbHelper.COLUMN_ID_NUM, MyDbHelper.COLUMN_NAME, MyDbHelper.COLUMN_GRAPH1,MyDbHelper.COLUMN_GRAPH2,MyDbHelper.COLUMN_GRAPH3,MyDbHelper.COLUMN_GRAPH4,MyDbHelper.COLUMN_MODEL1,MyDbHelper.COLUMN_MODEL2,MyDbHelper.COLUMN_GRAPH3,MyDbHelper.COLUMN_TEXT1,MyDbHelper.COLUMN_TEXT2,MyDbHelper.COLUMN_TEXT3,MyDbHelper.COLUMN_TEXT4)
        val selection = "${MyDbHelper.COLUMN_ID_NUM} LIKE ?"
        val selectionArgs = arrayOf(id.toString())
        val cursor = adapter.query(projection, selection, selectionArgs, null, null, null)
        // 读取查询结果并输出到日志
        cursor?.apply {
            if (moveToFirst()) {
                val idNum=findViewById<EditText>(R.id.et_idNum).text.toString()
                val name=findViewById<EditText>(R.id.et_name).text.toString()
                val graph1=findViewById<EditText>(R.id.et_graph1).text.toString()
                val graph2=findViewById<EditText>(R.id.et_graph2).text.toString()
                val graph3=findViewById<EditText>(R.id.et_graph3).text.toString()
                val graph4=findViewById<EditText>(R.id.et_graph4).text.toString()
                val model1=findViewById<EditText>(R.id.et_model1).text.toString()
                val model2=findViewById<EditText>(R.id.et_model2).text.toString()
                val model3=findViewById<EditText>(R.id.et_model3).text.toString()
                val text1=findViewById<EditText>(R.id.et_text1).text.toString()
                val text2=findViewById<EditText>(R.id.et_text2).text.toString()
                val text3=findViewById<EditText>(R.id.et_text3).text.toString()
                val text4=findViewById<EditText>(R.id.et_text4).text.toString()
                queryS="Record Details: id=$idNum, name=$name, graph1=$graph1,graph2=$graph2,graph3=$graph3,graph4=$graph4,model1=$model1,model2=$model2,model3=$model3,text1=$text1,text2=$text2,text3=$text3,text4=$text4"

                Log.i("main", "Record Details: id=$idNum, name=$name, graph1=$graph1,graph2=$graph2,graph3=$graph3,graph4=$graph4,model1=$model1,model2=$model2,model3=$model3,text1=$text1,text2=$text2,text3=$text3,text4=$text4")
            } else {
                Log.i("main", "No matching record found.")
            }
            close()
        }
    }}

class MyDbHelper(context: Context?) :
    SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        const val DATABASE_NAME = "mydb.db"
        const val DATABASE_VERSION = 2

        const val TABLE_NAME = "exhibit"

        const val COLUMN_ID_NUM = "idNum"
        const val COLUMN_NAME  ="name"
        const val COLUMN_GRAPH1 = "graph1"
        const val COLUMN_GRAPH2 = "graph2"
        const val COLUMN_GRAPH3 = "graph3"
        const val COLUMN_GRAPH4 = "graph4"
        const val COLUMN_MODEL1 = "model1"
        const val COLUMN_MODEL2 = "model2"
        const val COLUMN_MODEL3 = "model3"
        const val COLUMN_TEXT1= "text1"
        const val COLUMN_TEXT2= "text2"
        const val COLUMN_TEXT3= "text3"
        const val COLUMN_TEXT4= "text4"


    }
    override fun onCreate(db: SQLiteDatabase?) {
        val createTable = ("CREATE TABLE $TABLE_NAME ("
                + "$COLUMN_ID_NUM TEXT PRIMARY KEY,"
                + "$COLUMN_NAME TEXT,"
                + "$COLUMN_GRAPH1 TEXT,"
                + "$COLUMN_GRAPH2 TEXT,"
                + "$COLUMN_GRAPH3 TEXT,"
                + "$COLUMN_GRAPH4 TEXT,"
                + "$COLUMN_MODEL1 TEXT,"
                + "$COLUMN_MODEL2 TEXT,"
                + "$COLUMN_MODEL3 TEXT,"
                + "$COLUMN_TEXT1 TEXT,"
                + "$COLUMN_TEXT2 TEXT,"
                + "$COLUMN_TEXT3 TEXT,"
                + "$COLUMN_TEXT4 TEXT)")
        db?.execSQL(createTable)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        // 在该方法中更新数据库表格内容。
    }
}
class MyDbAdapter(private val context: Context) {

    private val dbHelper = MyDbHelper(context)
    fun insert(idNum: String?, name: String?,graph1: String?,graph2: String?,graph3: String?,graph4: String?,model1: String?,model2: String?,model3: String?,text1:String?,text2:String?,text3:String?,text4:String?): Long {
        val cv = ContentValues().apply {
            put(MyDbHelper.COLUMN_ID_NUM, idNum)//表格形式
            put(MyDbHelper.COLUMN_NAME, name)
            put(MyDbHelper.COLUMN_GRAPH1, graph1)
            put(MyDbHelper.COLUMN_GRAPH2, graph2)
            put(MyDbHelper.COLUMN_GRAPH3, graph3)
            put(MyDbHelper.COLUMN_GRAPH4, graph4)
            put(MyDbHelper.COLUMN_MODEL1, model1)
            put(MyDbHelper.COLUMN_MODEL2, model2)
            put(MyDbHelper.COLUMN_MODEL3, model3)
            put(MyDbHelper.COLUMN_TEXT1, text1)
            put(MyDbHelper.COLUMN_TEXT2, text2)
            put(MyDbHelper.COLUMN_TEXT3, text3)
            put(MyDbHelper.COLUMN_TEXT4, text4)
        }
        return dbHelper.writableDatabase.insert(MyDbHelper.TABLE_NAME, null, cv)
    }
    fun update(idNum: String?, name: String?,graph1: String?,graph2: String?,graph3: String?,graph4: String?,model1: String?,model2: String?,model3: String?,text1:String?,text2:String?,text3:String?,text4:String?): Int {
        val cv = ContentValues().apply {
            put(MyDbHelper.COLUMN_ID_NUM, idNum)
            put(MyDbHelper.COLUMN_NAME, name)
            put(MyDbHelper.COLUMN_GRAPH1, graph1)
            put(MyDbHelper.COLUMN_GRAPH2, graph2)
            put(MyDbHelper.COLUMN_GRAPH3, graph3)
            put(MyDbHelper.COLUMN_GRAPH4, graph4)
            put(MyDbHelper.COLUMN_MODEL1, model1)
            put(MyDbHelper.COLUMN_MODEL2, model2)
            put(MyDbHelper.COLUMN_MODEL3, model3)
            put(MyDbHelper.COLUMN_TEXT1, text1)
            put(MyDbHelper.COLUMN_TEXT2, text2)
            put(MyDbHelper.COLUMN_TEXT3, text3)
            put(MyDbHelper.COLUMN_TEXT4, text4)
        }
        val selection = "${MyDbHelper.COLUMN_ID_NUM} LIKE ?"
        val selectionArgs = arrayOf(idNum.toString())
        return dbHelper.writableDatabase.update(MyDbHelper.TABLE_NAME, cv, selection, selectionArgs)
    }

    fun delete(idNum: String?): Int {
        val selection = "${MyDbHelper.COLUMN_ID_NUM} LIKE ?"
        val selectionArgs = arrayOf(idNum.toString())
        return dbHelper.writableDatabase.delete(MyDbHelper.TABLE_NAME, selection, selectionArgs)
    }

    fun query(
        projection: Array<String>?,
        selection: String?,
        selectionArgs: Array<String>?,
        sortOrder: String?,
        nothing: Nothing?,
        nothing1: Nothing?
    ): Cursor {return dbHelper.readableDatabase.query(
        MyDbHelper.TABLE_NAME,  // 表格名称
        projection,             // 返回列集合，如果为 null 则返回所有列
        selection,              // WHERE 子句，不包括 WHERE 关键字，如果为 null 则不限制条件
        selectionArgs,          // WHERE 子句中占位符的实际值
        null,                   // 分组方式，不分组则为 null
        null,                   // 过滤器，不过滤数据则为 null
        sortOrder               // 排序条件，不排序则为 null
    )
    }
}